﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace DataServer.MySql
{
    public class SystemEditMS
    {
        AppCommon appcomm = new AppCommon();
        MySqlHander sqlHander = new MySqlHander();
        /*
        public DataSet GetMenu(String mid, String mname)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT 'False' as \"ADD\",'False' as \"MODIFY\",MID,MNAME,MCLASS,MODIFYER,DATE_FORMAT(MODIFYDATE,'%Y-%m-%d %H:%i:%s') MODIFYDATE,ISACTIVE FROM MENU_MAIN ");
                sb.Append("WHERE 1=1  ");
                if (mid.Length > 0)
                {
                    sb.Append(" AND MID='").Append(mid).Append("' ");
                }
                if (mname.Length > 0)
                {
                    sb.Append(" AND MNAME LIKE '").Append(mname).Append("%' ");
                }
                DataSet ds = MySqlHander.SqlSelect(sb.ToString());
                return ds;
            }catch(Exception e)
            {
                LogWrite.Log("ERROR", e.ToString());
                return null;
            }
        }
        //更新菜单表
        public String UpdateMenu(DataSet dsEdit, String userid)
        {
            int executecount = 0;
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                if (dsEdit.Tables["ADD"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO  MENU_MAIN(MID,MNAME,MCLASS,MODIFYER,MODIFYDATE,ISACTIVE) ");
                        sb.Append("VALUES(?MID,?MNAME,?MCLASS,?MODIFYER,NOW(),?ISACTIVE)");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?MID", MySqlDbType.String);
                        cmd.Parameters.Add("?MNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?MCLASS", MySqlDbType.String);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
                        cmd.Parameters["?MID"].Value = dr["MID"].ToString();
                        cmd.Parameters["?MNAME"].Value = dr["MNAME"].ToString();
                        cmd.Parameters["?MCLASS"].Value = dr["MCLASS"].ToString();
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        int iat=0;
                        if ("1".Equals(dr["ISACTIVE"].ToString()) || "True".Equals(dr["ISACTIVE"].ToString()))
                        {
                            iat = 1;
                        }
                        cmd.Parameters["?ISACTIVE"].Value = iat;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " Add Menu MID=" + dr["MID"].ToString() + "  MNAME=" + dr["MNAME"].ToString() + "  CLASSNAME=" + dr["MCLASS"].ToString();
                        LogWrite.Log("SYSTEM", strLog);

                    }

                }
                if (dsEdit.Tables["MODIFY"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["MODIFY"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("UPDATE  MENU_MAIN SET MNAME=?MNAME,MCLASS=?MCLASS,MODIFYER=?MODIFYER,MODIFYDATE=NOW(),ISACTIVE=?ISACTIVE ");
                        sb.Append(" WHERE MID=?MID");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;                        
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?MID", MySqlDbType.String);                        
                        cmd.Parameters.Add("?MNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?MCLASS", MySqlDbType.String);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
                        cmd.Parameters["?MID"].Value = dr["MID"].ToString();
                        cmd.Parameters["?MNAME"].Value = dr["MNAME"].ToString();
                        cmd.Parameters["?MCLASS"].Value = dr["MCLASS"].ToString();
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        int iat = 0;
                        if ("1".Equals(dr["ISACTIVE"].ToString()) || "True".Equals(dr["ISACTIVE"].ToString()))
                        {
                            iat = 1;
                        }
                        cmd.Parameters["?ISACTIVE"].Value = iat;
                        executecount+=cmd.ExecuteNonQuery();

                        String strLog = userid + " UPDATE  Menu MID=" + dr["MID"].ToString() + "  MNAME=" + dr["MNAME"].ToString() + "  CLASSNAME=" + dr["MCLASS"].ToString();
                        LogWrite.Log("SYSTEM", strLog);

                    }
                }
                transaction.Commit();
                
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Update/Add :" + executecount + " ";
            return rtMsg;
        }
        //取组别信息
        public DataSet GetGroupByID(String gid,Boolean isactive)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT 'False' as \"ADD\",'False' as \"MODIFY\",G.GID,G.GNAME,U.USERNAME MODIFYER,DATE_FORMAT(G.MODIFYDATE,'%Y-%m-%d %H:%i:%s') MODIFYDATE,G.ISACTIVE FROM GROUP_MAIN G ");
                sb.Append("LEFT JOIN USER_MAIN U ON G.MODIFYER=U.USERID WHERE 1=1  ");
                if (gid.Length > 0)
                {
                    sb.Append(" AND G.GID='").Append(gid).Append("' ");
                }
                if (isactive)
                {
                    sb.Append(" AND G.ISACTIVE=1");
                }
                
                DataSet ds = MySqlHander.SqlSelect(sb.ToString());
                return ds;
            }
            catch (Exception e)
            {
                LogWrite.Log("ERROR", e.ToString());
                return null;
            }
        }
        //取组别对应的菜单配置信息
        public DataSet GetGrouMenuSet(String gid)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                if (gid.Length > 0)
                {
                    sb.Append("SELECT 'False' AS \"ADD\", 'False' AS \"MODIFY\", G.GID, G.MID, M.MNAME, G.ISVISIBLE, U.USERNAME MODIFYER, DATE_FORMAT(G.MODIFYDATE, '%Y-%m-%d %H:%i:%s') MODIFYDATE ");
                    sb.Append("FROM GROUP_MENU G  LEFT JOIN USER_MAIN U    ON G.MODIFYER = U.USERID ");
                    sb.Append("LEFT JOIN MENU_MAIN M    ON M.MID = G.MID  WHERE G.GID = '").Append(gid).Append("' ");
                    sb.Append("UNION ");
                    sb.Append("SELECT 'True' AS \"ADD\", 'False' AS \"MODIFY\", '").Append(gid).Append("' AS GID, M.MID, M.MNAME, 0 ISVISIBLE, NULL MODIFYER, NULL MODIFYDATE ");
                    sb.Append("FROM MENU_MAIN M WHERE M.MID NOT IN (SELECT G.MID FROM GROUP_MENU G WHERE G.GID = '").Append(gid).Append("') ");
                    sb.Append(" ORDER BY MID");
                }
                else
                {
                    sb.Append("SELECT 'False' AS \"ADD\", 'False' AS \"MODIFY\", G.GID, G.MID, M.MNAME, G.ISVISIBLE, U.USERNAME MODIFYER, DATE_FORMAT(G.MODIFYDATE, '%Y-%m-%d %H:%i:%s') MODIFYDATE ");
                    sb.Append("FROM GROUP_MENU G  LEFT JOIN USER_MAIN U    ON G.MODIFYER = U.USERID ");
                    sb.Append("LEFT JOIN MENU_MAIN M    ON M.MID = G.MID  WHERE G.GID = ' ' ");
                }


                DataSet ds = MySqlHander.SqlSelect(sb.ToString());
                return ds;
                
                
            }
            catch (Exception e)
            {
                LogWrite.Log("ERROR", e.ToString());
                return null;
            }

        }

        //取表中某字段的最大值
        public String getMaxId(String tablename, String column)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT IFNULL(MAX(").Append(column).Append("),'0') FROM ").Append(tablename);
                DataSet ds = MySqlHander.SqlSelect(sb.ToString());
                return ds.Tables[0].Rows[0][0].ToString();

            }catch(Exception e)
            {
                LogWrite.Log("ERROR", e.ToString());
                return null;
            }
        }

        //更新组别信息
        public String UpdateGroup(DataSet dsEdit, String userid)
        {
            int executecount = 0;
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                if (dsEdit.Tables["ADD"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO  GROUP_MAIN(GID,GNAME,MODIFYER,MODIFYDATE,ISACTIVE) ");
                        sb.Append("VALUES(?GID,?GNAME,?MODIFYER,NOW(),?ISACTIVE)");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?GID", MySqlDbType.String);
                        cmd.Parameters.Add("?GNAME", MySqlDbType.String);                      
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
                        cmd.Parameters["?GID"].Value = dr["GID"].ToString();
                        cmd.Parameters["?GNAME"].Value = dr["GNAME"].ToString();                       
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        int iat = 0;
                        if ("1".Equals(dr["ISACTIVE"].ToString()) || "True".Equals(dr["ISACTIVE"].ToString()))
                        {
                            iat = 1;
                        }
                        cmd.Parameters["?ISACTIVE"].Value = iat;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " Add Group GID=" + dr["GID"].ToString() + "  GNAME=" + dr["GNAME"].ToString() ;
                        LogWrite.Log("SYSTEM", strLog);

                    }

                }
                if (dsEdit.Tables["MODIFY"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["MODIFY"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("UPDATE  GROUP_MAIN SET GNAME=?GNAME,MODIFYER=?MODIFYER,MODIFYDATE=NOW(),ISACTIVE=?ISACTIVE ");
                        sb.Append(" WHERE GID=?GID");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?GID", MySqlDbType.String);
                        cmd.Parameters.Add("?GNAME", MySqlDbType.String);                        
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
                        cmd.Parameters["?GID"].Value = dr["GID"].ToString();
                        cmd.Parameters["?GNAME"].Value = dr["GNAME"].ToString();                        
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        int iat = 0;
                        if ("1".Equals(dr["ISACTIVE"].ToString()) || "True".Equals(dr["ISACTIVE"].ToString()))
                        {
                            iat = 1;
                        }
                        cmd.Parameters["?ISACTIVE"].Value = iat;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " UPDATE  Group GID=" + dr["GID"].ToString() + "  GNAME=" + dr["GNAME"].ToString();
                        LogWrite.Log("SYSTEM", strLog);

                    }
                }
                transaction.Commit();

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Update/Add :" + executecount + " ";
            return rtMsg;
        }

        //更新组别菜单权限
        public String UpdateGroupMenu(DataSet dsEdit, String userid)
        {
            int executecount = 0;
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                if (dsEdit.Tables["ADD"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO  GROUP_MENU(GID,MID,ISVISIBLE,MODIFYER,MODIFYDATE) ");
                        sb.Append("VALUES(?GID,?MID,?ISVISIBLE,?MODIFYER,NOW())");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?GID", MySqlDbType.String);
                        cmd.Parameters.Add("?MID", MySqlDbType.String);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters.Add("?ISVISIBLE", MySqlDbType.Int32);
                        cmd.Parameters["?GID"].Value = dr["GID"].ToString();
                        cmd.Parameters["?MID"].Value = dr["MID"].ToString();
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        int iat = 0;
                        if ("1".Equals(dr["ISVISIBLE"].ToString()) || "True".Equals(dr["ISVISIBLE"].ToString()))
                        {
                            iat = 1;
                        }
                        cmd.Parameters["?ISVISIBLE"].Value = iat;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " Add GroupMenu GID=" + dr["GID"].ToString() + "  MID=" + dr["MID"].ToString() + "  ISVISIBLE=" + iat;
                        LogWrite.Log("SYSTEM", strLog);

                    }

                }
                if (dsEdit.Tables["MODIFY"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["MODIFY"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("UPDATE  GROUP_MENU SET MODIFYER=?MODIFYER,MODIFYDATE=NOW(),ISVISIBLE=?ISVISIBLE ");
                        sb.Append(" WHERE GID=?GID AND MID=?MID");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?GID", MySqlDbType.String);
                        cmd.Parameters.Add("?MID", MySqlDbType.String);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters.Add("?ISVISIBLE", MySqlDbType.Int32);
                        cmd.Parameters["?GID"].Value = dr["GID"].ToString();
                        cmd.Parameters["?MID"].Value = dr["MID"].ToString();
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        int iat = 0;
                        if ("1".Equals(dr["ISVISIBLE"].ToString()) || "True".Equals(dr["ISVISIBLE"].ToString()))
                        {
                            iat = 1;
                        }
                        cmd.Parameters["?ISVISIBLE"].Value = iat;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " UPDATE  GroupMenu GID=" + dr["GID"].ToString() + "  MID=" + dr["MID"].ToString()+"  ISVISIBLE=" + iat;
                        LogWrite.Log("SYSTEM", strLog);

                    }
                }
                transaction.Commit();

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Update/Add :" + executecount + " ";
            return rtMsg;
        }

        //通过用户名和用户ID得到用户
        public DataSet GetUserByID(String userid, String username)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT  'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\",U.USERID, U.USERNAME,U.PASSWORD,U.PWERROE_TIMES,U.EMAIL,U.PHONE,U.ISDEL,U.MODIFYER,DATE_FORMAT(U.MODIFYDATE,'%Y-%m-%d %H:%i:%s') MODIFYDATE FROM USER_MAIN U ");

            sb.Append("WHERE U.ISDEL!=1 ");
            if (userid.Length > 0)
            {
                sb.Append(" AND U.USERID='").Append(userid).Append("'");
            }
            if (username.Length > 0)
            {
                sb.Append(" AND U.USERNAME LIKE '").Append(username).Append("%'");
            }

            DataSet ds = MySqlHander.SqlSelect(sb.ToString());
            return ds;

        }
        //更新用户密码
        public String UpdatePassword(String userid, String password)
        {
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                StringBuilder sb = new StringBuilder();
                sb.Append("UPDATE USER_MAIN SET PASSWORD=?PASSWORD,MODIFYER=?MODIFYER,MODIFYDATE=NOW() WHERE USERID=?USERID  ");                

                cmd.CommandText = sb.ToString();
                cmd.Transaction = transaction;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("?USERID", MySqlDbType.String);
                cmd.Parameters.Add("?PASSWORD", MySqlDbType.String);
                cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);               
                cmd.Parameters["?USERID"].Value = userid;
                cmd.Parameters["?PASSWORD"].Value = password;
                cmd.Parameters["?MODIFYER"].Value = userid;              
                
                if (cmd.ExecuteNonQuery() == 1)
                {
                    rtMsg = "Change Passwrod OK.";
                }
                else
                {
                    rtMsg = "Change Passwrod Fail.";
                }
                transaction.Commit();

                String strLog = userid + " UPDATE PASSWORD   USERID=" + userid + "  PASSWORD=" + password;
                LogWrite.Log("SYSTEM", strLog);
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Change Passwrod OK.";
            return rtMsg;
        }
        //取得还没分配给用户的群组信息
        public DataSet GetGroupNotSign(String userid)
        {
            StringBuilder sb =new  StringBuilder();
            sb.Append("SELECT G.GID,G.GNAME FROM GROUP_MAIN G WHERE G.ISACTIVE=1 AND G.GID NOT IN ( SELECT UG.GID FROM USER_GROUP UG WHERE UG.USERID='");
            sb.Append(userid).Append("')");
            return MySqlHander.SqlSelect(sb.ToString());

        }
        //取得已经分配给用户的群组信息
        public DataSet GetGroupSigned(String userid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT 'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\",UG.USERID,UG.GID,G.GNAME FROM USER_GROUP UG LEFT JOIN GROUP_MAIN G ON UG.GID=G.GID WHERE UG.USERID='");
            sb.Append(userid).Append("'");
            return MySqlHander.SqlSelect(sb.ToString());
        }
        //更新人员对应群组表
        public String UpdateUserGroup(DataSet dsEdit, String userid)
        {
            int executecount = 0;
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                if (dsEdit.Tables["ADD"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO  USER_GROUP(USERID,GID,MODIFYER,MODIFYDATE) ");
                        sb.Append("VALUES(?USERID,?GID,?MODIFYER,NOW())");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?USERID", MySqlDbType.String);
                        cmd.Parameters.Add("?GID", MySqlDbType.String);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters["?USERID"].Value = dr["USERID"].ToString();
                        cmd.Parameters["?GID"].Value = dr["GID"].ToString();
                        cmd.Parameters["?MODIFYER"].Value = userid;                        
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " Add User Group USERID=" + dr["USERID"].ToString() + "  GID=" + dr["GID"].ToString();
                        LogWrite.Log("SYSTEM", strLog);

                    }

                }
                if (dsEdit.Tables["DELETE"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["DELETE"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("DELETE FROM USER_GROUP  ");
                        sb.Append(" WHERE GID=?GID AND USERID=?USERID");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?GID", MySqlDbType.String);
                        cmd.Parameters.Add("?USERID", MySqlDbType.String);
                        
                        cmd.Parameters["?GID"].Value = dr["GID"].ToString();
                        cmd.Parameters["?USERID"].Value = dr["USERID"].ToString();                        
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " DELETE  User_Group GID=" + dr["GID"].ToString() + "  USERID=" + dr["USERID"].ToString();
                        LogWrite.Log("SYSTEM", strLog);

                    }
                }
                transaction.Commit();

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Update/Add :" + executecount + " ";
            return rtMsg;
        
        }
        //查询个人的部门权限
        public DataSet GetDepartmentSigned(String userid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT  'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\",A.USERID, A.DPMID, D.DPMNAME, D.DPMENAME, D.DPMMARK FROM   USER_AUTHORITY A   LEFT JOIN   DEPARTMENT_MAIN D ON A.DPMID = D.DPMID");
            sb.Append(" WHERE  A.USERID='").Append(userid).Append("'");
           
            return MySqlHander.SqlSelect(sb.ToString());
        }
        //取部门所有数据
        public DataSet GetDepartment(Boolean isactive)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT 'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\",D.* FROM DEPARTMENT_MAIN D ");
            if (isactive)
            {
                sb.Append(" WHERE D.ISACTIVE=1 ");
            }
            

            return MySqlHander.SqlSelect(sb.ToString());
        }
        //更新人员部门权限
        public String UpdateUserAuthority(DataSet dsEdit, String userid)
        {
            int executecount = 0;
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                if (dsEdit.Tables["ADD"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO  USER_AUTHORITY(USERID,DPMID,MODIFYER,MODIFYDATE) ");
                        sb.Append("VALUES(?USERID,?DPMID,?MODIFYER,NOW())");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?USERID", MySqlDbType.String);
                        cmd.Parameters.Add("?DPMID", MySqlDbType.String);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters["?USERID"].Value = dr["USERID"].ToString();
                        cmd.Parameters["?DPMID"].Value = dr["DPMID"].ToString();
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " Add User Authority USERID=" + dr["USERID"].ToString() + "  DPMID=" + dr["DPMID"].ToString();
                        LogWrite.Log("SYSTEM", strLog);

                    }

                }
                if (dsEdit.Tables["DELETE"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["DELETE"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("DELETE FROM USER_AUTHORITY  ");
                        sb.Append(" WHERE DPMID=?DPMID AND USERID=?USERID");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?DPMID", MySqlDbType.String);
                        cmd.Parameters.Add("?USERID", MySqlDbType.String);

                        cmd.Parameters["?DPMID"].Value = dr["DPMID"].ToString();
                        cmd.Parameters["?USERID"].Value = dr["USERID"].ToString();
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " DELETE  User_Authority DPMID=" + dr["DPMID"].ToString() + "  USERID=" + dr["USERID"].ToString();
                        LogWrite.Log("SYSTEM", strLog);

                    }
                }
                transaction.Commit();

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Update/Add :" + executecount + " ";
            return rtMsg;
        }
        //查询字典表主表的数据
        public DataSet GetDictionaryMain(Boolean isactive)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT 'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\",D.* FROM DICTIONARY_MAIN D ");
            //if (isactive)
            //{
            //    sb.Append(" WHERE D.ISACTIVE=1 ");
            //}


            return MySqlHander.SqlSelect(sb.ToString());
        }
        //查询字典详情
        public DataSet GetDictionaryDetail(String dcid, Boolean isactive)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT 'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\",D.* FROM DICTIONARY_DETAIL D  WHERE 1=1 ");
            if (isactive)
            {
                sb.Append(" AND D.ISACTIVE=1 ");
            }
            if (dcid.Length>0)
            {
                sb.Append(" AND D.DICID='").Append(dcid).Append("'");
            }
            return MySqlHander.SqlSelect(sb.ToString());
        }
        //查询字典子表最大ID
        public String GetDicDetailMaxid(String dcid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT IFNULL(MAX(CODEID),'0') FROM DICTIONARY_DETAIL WHERE DICID='").Append(dcid).Append("'");
            DataSet ds = MySqlHander.SqlSelect(sb.ToString());
            return ds.Tables[0].Rows[0][0].ToString();

        }
        //更新字典主表
        public String UpdateDicMain(DataSet dsEdit, String userid)
        {
            int executecount = 0;
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                if (dsEdit.Tables["ADD"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO  DICTIONARY_MAIN(DICID,DICNAME,DICENNAME,MODIFYER,MODIFYDATE) ");
                        sb.Append("VALUES(?DICID,?DICNAME,?DICENNAME,?MODIFYER,NOW())");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?DICID", MySqlDbType.String);
                        cmd.Parameters.Add("?DICNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?DICENNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters["?DICID"].Value = dr["DICID"].ToString();
                        cmd.Parameters["?DICNAME"].Value = dr["DICNAME"].ToString();
                        cmd.Parameters["?DICENNAME"].Value = dr["DICENNAME"].ToString();
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " Add DICTIONARY DICID=" + dr["DICID"].ToString() + "  DICNAME=" + dr["DICNAME"].ToString() + "  DICENNAME=" + dr["DICENNAME"].ToString();
                        LogWrite.Log("SYSTEM", strLog);

                    }

                }
                if (dsEdit.Tables["MODIFY"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["MODIFY"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("UPDATE  DICTIONARY_MAIN  ");
                        sb.Append(" SET DICNAME=?DICNAME,DICENNAME=?DICENNAME,MODIFYER=?MODIFYER,MODIFYDATE=NOW() WHERE DICID=?DICID");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?DICID", MySqlDbType.String);
                        cmd.Parameters.Add("?DICNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?DICENNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters["?DICID"].Value = dr["DICID"].ToString();
                        cmd.Parameters["?DICNAME"].Value = dr["DICNAME"].ToString();
                        cmd.Parameters["?DICENNAME"].Value = dr["DICENNAME"].ToString();
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " UPDATE DICTIONARY DICID=" + dr["DICID"].ToString() + "  DICNAME=" + dr["DICNAME"].ToString() + "  DICENNAME=" + dr["DICENNAME"].ToString();
                        LogWrite.Log("SYSTEM", strLog);


                    }
                }
                transaction.Commit();

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Update/Add :" + executecount + " ";
            return rtMsg;
        }
        //更新字典子表
        public String UpdateDicDetail(DataSet dsEdit, String userid)
        {
            int executecount = 0;
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                if (dsEdit.Tables["ADD"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO  DICTIONARY_DETAIL(DICID,CODEID,CODENAME,CODEENNAME,ISACTIVE,MODIFYER,MODIFYDATE) ");
                        sb.Append("VALUES(?DICID,?CODEID,?CODENAME,?CODEENNAME,?ISACTIVE,?MODIFYER,NOW())");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?DICID", MySqlDbType.String);
                        cmd.Parameters.Add("?CODEID", MySqlDbType.String);
                        cmd.Parameters.Add("?CODENAME", MySqlDbType.String);
                        cmd.Parameters.Add("?CODEENNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters["?DICID"].Value = dr["DICID"].ToString();
                        cmd.Parameters["?CODEID"].Value = dr["CODEID"].ToString();
                        cmd.Parameters["?CODENAME"].Value = dr["CODENAME"].ToString();
                        cmd.Parameters["?CODEENNAME"].Value = dr["CODEENNAME"].ToString();
                        int isat = 0;
                        if ("True".Equals(dr["ISACTIVE"].ToString()) || "1".Equals(dr["ISACTIVE"].ToString()))
                        {
                            isat = 1;
                        }
                        cmd.Parameters["?ISACTIVE"].Value = isat;
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " Add DICTIONARY_DETAIL DICID=" + dr["DICID"].ToString() + "  CODEID=" + dr["CODEID"].ToString() + "  CODENAME=" + dr["CODENAME"].ToString() + "  CODEENNAME=" + dr["CODEENNAME"].ToString() + "  ISACTIVE=" + isat;
                        LogWrite.Log("SYSTEM", strLog);

                    }

                }
                if (dsEdit.Tables["MODIFY"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["MODIFY"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("UPDATE  DICTIONARY_DETAIL  ");
                        sb.Append(" SET CODENAME=?CODENAME,CODEENNAME=?CODEENNAME,ISACTIVE=?ISACTIVE,MODIFYER=?MODIFYER,MODIFYDATE=NOW() WHERE DICID=?DICID AND CODEID=?CODEID ");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?DICID", MySqlDbType.String);
                        cmd.Parameters.Add("?CODEID", MySqlDbType.String);
                        cmd.Parameters.Add("?CODENAME", MySqlDbType.String);
                        cmd.Parameters.Add("?CODEENNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters["?DICID"].Value = dr["DICID"].ToString();
                        cmd.Parameters["?CODEID"].Value = dr["CODEID"].ToString();
                        cmd.Parameters["?CODENAME"].Value = dr["CODENAME"].ToString();
                        cmd.Parameters["?CODEENNAME"].Value = dr["CODEENNAME"].ToString();
                        int isat = 0;
                        if ("True".Equals(dr["ISACTIVE"].ToString()) || "1".Equals(dr["ISACTIVE"].ToString()))
                        {
                            isat = 1;
                        }
                        cmd.Parameters["?ISACTIVE"].Value = isat;
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " UPDATE DICTIONARY_DETAIL DICID=" + dr["DICID"].ToString() + "  CODEID=" + dr["CODEID"].ToString() + "  CODENAME=" + dr["CODENAME"].ToString() + "  CODEENNAME=" + dr["CODEENNAME"].ToString() + "  ISACTIVE=" + isat;
                        LogWrite.Log("SYSTEM", strLog);


                    }
                }
                transaction.Commit();

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Update/Add :" + executecount + " ";
            return rtMsg;
        }
        //取下级部门码最大值
        public String GetDepartmentMaxId(String dptid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT IFNULL(MAX(DPMID),'0') FROM DEPARTMENT_MAIN WHERE DPMID LIKE '");
            sb.Append(dptid).Append("%' AND LENGTH(DPMID)=").Append(dptid.Length+2);
            DataSet ds = MySqlHander.SqlSelect(sb.ToString());
            return ds.Tables[0].Rows[0][0].ToString();
        }
        //更新部门信息表
        public String UpdateDepartment(DataSet dsEdit, String userid)
        {
            int executecount = 0;
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                if (dsEdit.Tables["ADD"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO  DEPARTMENT_MAIN(DPMID,DPMNAME,DPMENAME,DPMMARK,DPMOWNER,ISACTIVE,MODIFYER,MODIFYDATE) ");
                        sb.Append("VALUES(?DPMID,?DPMNAME,?DPMENAME,?DPMMARK,?DPMOWNER,?ISACTIVE,?MODIFYER,NOW())");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?DPMID", MySqlDbType.String);
                        cmd.Parameters.Add("?DPMNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?DPMENAME", MySqlDbType.String);
                        cmd.Parameters.Add("?DPMMARK", MySqlDbType.String);
                        cmd.Parameters.Add("?DPMOWNER", MySqlDbType.String);
                        cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters["?DPMID"].Value = dr["DPMID"].ToString();
                        cmd.Parameters["?DPMNAME"].Value = dr["DPMNAME"].ToString();
                        cmd.Parameters["?DPMENAME"].Value = dr["DPMENAME"].ToString();
                        cmd.Parameters["?DPMMARK"].Value = dr["DPMMARK"].ToString();
                        cmd.Parameters["?DPMOWNER"].Value = dr["DPMOWNER"].ToString();
                        cmd.Parameters["?ISACTIVE"].Value = Int32.Parse(dr["ISACTIVE"].ToString());
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " Add DEPARTMENT DPMID=" + dr["DPMID"].ToString() + "  DPMNAME=" + dr["DPMNAME"].ToString() + "  DPMENAME=" + dr["DPMENAME"].ToString();
                        strLog = strLog + "  DPMMARK=" + dr["DPMMARK"].ToString() + "  DPMOWNER=" + dr["DPMOWNER"].ToString() + "  ISACTIVE=" + dr["ISACTIVE"].ToString();
                        LogWrite.Log("SYSTEM", strLog);

                    }

                }
                if (dsEdit.Tables["MODIFY"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["MODIFY"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("UPDATE  DEPARTMENT_MAIN  ");
                        sb.Append(" SET DPMNAME=?DPMNAME,DPMENAME=?DPMENAME,DPMMARK=?DPMMARK,DPMOWNER=?DPMOWNER,ISACTIVE=?ISACTIVE,MODIFYER=?MODIFYER,MODIFYDATE=NOW() WHERE DPMID=?DPMID");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?DPMID", MySqlDbType.String);
                        cmd.Parameters.Add("?DPMNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?DPMENAME", MySqlDbType.String);
                        cmd.Parameters.Add("?DPMMARK", MySqlDbType.String);
                        cmd.Parameters.Add("?DPMOWNER", MySqlDbType.String);
                        cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters["?DPMID"].Value = dr["DPMID"].ToString();
                        cmd.Parameters["?DPMNAME"].Value = dr["DPMNAME"].ToString();
                        cmd.Parameters["?DPMENAME"].Value = dr["DPMENAME"].ToString();
                        cmd.Parameters["?DPMMARK"].Value = dr["DPMMARK"].ToString();
                        cmd.Parameters["?DPMOWNER"].Value = dr["DPMOWNER"].ToString();
                        cmd.Parameters["?ISACTIVE"].Value = Int32.Parse(dr["ISACTIVE"].ToString());
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " UPDATE DEPARTMENT DPMID=" + dr["DPMID"].ToString() + "  DPMNAME=" + dr["DPMNAME"].ToString() + "  DPMENAME=" + dr["DPMENAME"].ToString();
                        strLog = strLog + "  DPMMARK=" + dr["DPMMARK"].ToString() + "  DPMOWNER=" + dr["DPMOWNER"].ToString() + "  ISACTIVE=" + dr["ISACTIVE"].ToString();
                        LogWrite.Log("SYSTEM", strLog);


                    }
                }
                transaction.Commit();

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Update/Add :" + executecount + " ";
            return rtMsg;
        }
        //得到同级的所有部门
        public DataSet GetSameLevelDepartment(String dpmid, Boolean isactive)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT 'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\",D.* FROM DEPARTMENT_MAIN D  WHERE 1=1 ");
            if (isactive)
            {
                sb.Append(" AND D.ISACTIVE=1 ");
            }
            if (dpmid.Length > 0)
            {
                sb.Append(" AND D.DPMID LIKE '").Append(dpmid.Substring(0, dpmid.Length - 2)).Append("%' AND LENGTH(D.DPMID)=").Append(dpmid.Length);
            }
            return MySqlHander.SqlSelect(sb.ToString());
        }
        ////得到职位级次
        //public DataSet GetPostLevelByID(String plid, String plname, Boolean isactive)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append("SELECT 'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\",D.PLID,D.PLNAME,D.PLENAME,D.PLMARK,D.ISACTIVE,D.MODIFYER,DATE_FORMAT(D.MODIFYDATE, '%Y-%m-%d %H:%i:%s') MODIFYDATE  FROM POSTLEVEL_MAIN D  WHERE 1=1 ");
        //    if (isactive)
        //    {
        //        sb.Append(" AND D.ISACTIVE=1 ");
        //    }
        //    if (plid.Length > 0)
        //    {
        //        sb.Append(" AND D.PLID = '").Append(plid).Append("' ");
        //    }
        //    if (plname.Length > 0)
        //    {
        //        sb.Append(" AND D.PLNAME  LIKE '").Append(plname).Append("%' ");
        //    }

        //    sb.Append("ORDER BY D.PLID ");
        //    return MySqlHander.SqlSelect(sb.ToString());
        //}
        //更新职级表
        //public String UpatePostLevel(DataSet dsEdit, String userid)
        //{
        //    int executecount = 0;
        //    string rtMsg = "";
        //    DbConnectionmysql connection = new DbConnectionmysql();
        //    connection.mysqlconnect.Open();

        //    MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
        //    try
        //    {
        //        if (dsEdit.Tables["ADD"] != null)
        //        {
        //            foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
        //            {
        //                MySqlCommand cmdc = (MySqlCommand)connection.mysqlconnect.CreateCommand();
        //                StringBuilder sbc = new StringBuilder();
        //                sbc.Append("SELECT IFNULL(COUNT(*),0) RNUM FROM POSTLEVEL_MAIN WHERE PLID='").Append(dr["PLID"].ToString()).Append("' ");
        //                cmdc.CommandText = sbc.ToString();
        //                cmdc.Transaction = transaction;
        //                int rnum = Int32.Parse(cmdc.ExecuteScalar().ToString());
        //                if (rnum > 0)
        //                {
        //                    return "PLID Duplicate!!";
        //                }

        //                MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
        //                StringBuilder sb = new StringBuilder();
        //                sb.Append("INSERT INTO  POSTLEVEL_MAIN(PLID,PLNAME,PLENAME,PLMARK,ISACTIVE,MODIFYER,MODIFYDATE) ");
        //                sb.Append("VALUES(?PLID,?PLNAME,?PLENAME,?PLMARK,?ISACTIVE,?MODIFYER,NOW())");

        //                cmd.CommandText = sb.ToString();
        //                cmd.Transaction = transaction;
        //                cmd.Parameters.Clear();
        //                cmd.Parameters.Add("?PLID", MySqlDbType.String);
        //                cmd.Parameters.Add("?PLNAME", MySqlDbType.String);
        //                cmd.Parameters.Add("?PLENAME", MySqlDbType.String);
        //                cmd.Parameters.Add("?PLMARK", MySqlDbType.String);
        //                cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
        //                cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
        //                cmd.Parameters["?PLID"].Value = dr["PLID"].ToString();
        //                cmd.Parameters["?PLNAME"].Value = dr["PLNAME"].ToString();
        //                cmd.Parameters["?PLENAME"].Value = dr["PLENAME"].ToString();
        //                cmd.Parameters["?PLMARK"].Value = dr["PLMARK"].ToString();
        //                int isat = 0;
        //                if ("True".Equals(dr["ISACTIVE"].ToString()) || "1".Equals(dr["ISACTIVE"].ToString()))
        //                {
        //                    isat = 1;
        //                }
        //                cmd.Parameters["?ISACTIVE"].Value = isat;
        //                cmd.Parameters["?MODIFYER"].Value = userid;
        //                executecount += cmd.ExecuteNonQuery();

        //                String strLog = userid + " Add POST_LEVEL PLID=" + dr["PLID"].ToString() + "  PLNAME=" + dr["PLNAME"].ToString() + "  PLENAME=" + dr["PLENAME"].ToString() + "  PLMARK=" + dr["PLMARK"].ToString() + "  ISACTIVE=" + isat;
        //                LogWrite.Log("SYSTEM", strLog);

        //            }

        //        }
        //        if (dsEdit.Tables["MODIFY"] != null)
        //        {
        //            foreach (DataRow dr in dsEdit.Tables["MODIFY"].Rows)
        //            {
        //                MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
        //                StringBuilder sb = new StringBuilder();
        //                sb.Append("UPDATE  POSTLEVEL_MAIN  ");
        //                sb.Append(" SET PLNAME=?PLNAME,PLENAME=?PLENAME,PLMARK=?PLMARK,ISACTIVE=?ISACTIVE,MODIFYER=?MODIFYER,MODIFYDATE=NOW() WHERE PLID=?PLID  ");

        //                cmd.CommandText = sb.ToString();
        //                cmd.Transaction = transaction;
        //                cmd.Parameters.Clear();
        //                cmd.Parameters.Add("?PLID", MySqlDbType.String);
        //                cmd.Parameters.Add("?PLNAME", MySqlDbType.String);
        //                cmd.Parameters.Add("?PLENAME", MySqlDbType.String);
        //                cmd.Parameters.Add("?PLMARK", MySqlDbType.String);
        //                cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
        //                cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
        //                cmd.Parameters["?PLID"].Value = dr["PLID"].ToString();
        //                cmd.Parameters["?PLNAME"].Value = dr["PLNAME"].ToString();
        //                cmd.Parameters["?PLENAME"].Value = dr["PLENAME"].ToString();
        //                cmd.Parameters["?PLMARK"].Value = dr["PLMARK"].ToString();
        //                int isat = 0;
        //                if ("True".Equals(dr["ISACTIVE"].ToString()) || "1".Equals(dr["ISACTIVE"].ToString()))
        //                {
        //                    isat = 1;
        //                }
        //                cmd.Parameters["?ISACTIVE"].Value = isat;
        //                cmd.Parameters["?MODIFYER"].Value = userid;
        //                executecount += cmd.ExecuteNonQuery();

        //                String strLog = userid + " UPDATE POST_LEVEL PLID=" + dr["PLID"].ToString() + "  PLNAME=" + dr["PLNAME"].ToString() + "  PLENAME=" + dr["PLENAME"].ToString() + "  PLMARK=" + dr["PLMARK"].ToString() + "  ISACTIVE=" + isat;
        //                LogWrite.Log("SYSTEM", strLog);


        //            }
        //        }
        //        transaction.Commit();

        //    }
        //    catch (Exception ex)
        //    {
        //        transaction.Rollback();
        //        return ex.ToString();
        //    }
        //    finally
        //    {
        //        connection.mysqlconnect.Close();
        //    }
        //    rtMsg = "Update/Add :" + executecount + " ";
        //    return rtMsg;
        //}
        //得到岗位信息
        //public DataSet GetPostTitleByID(String ptid, String ptname, Boolean isactive)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append("SELECT 'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\",P.PTID,P.PTNAME, P.PTENAME,P.PTMARK, P.DPMID, P.ISACTIVE,P.MODIFYER,DATE_FORMAT(P.MODIFYDATE, '%Y-%m-%d %H:%i:%s') MODIFYDATE, D.DPMNAME, ");
        //    sb.Append("CASE WHEN length(IFNULL(P.DPMID, ''))=0 THEN NULL  WHEN length(IFNULL(P.DPMID, ''))>=4 THEN (SELECT DPMNAME FROM  department_main  WHERE DPMID = SUBSTRING(P.DPMID,1,4))  ELSE NULL  END DEPARTMENT1 , ");
        //    sb.Append("CASE WHEN length(IFNULL(P.DPMID, ''))=0 THEN NULL  WHEN length(IFNULL(P.DPMID, ''))>=6 THEN (SELECT DPMNAME FROM  department_main  WHERE DPMID = SUBSTRING(P.DPMID,1,6))  ELSE NULL  END DEPARTMENT2 , ");
        //    sb.Append("CASE WHEN length(IFNULL(P.DPMID, ''))=0 THEN NULL  WHEN length(IFNULL(P.DPMID, ''))>=8 THEN (SELECT DPMNAME FROM  department_main  WHERE DPMID = SUBSTRING(P.DPMID,1,8))  ELSE NULL  END DEPARTMENT3 , ");
        //    sb.Append("CASE WHEN length(IFNULL(P.DPMID, ''))=0 THEN NULL  WHEN length(IFNULL(P.DPMID, ''))>=10 THEN (SELECT DPMNAME FROM  department_main  WHERE DPMID = SUBSTRING(P.DPMID,1,10))  ELSE NULL  END DEPARTMENT4  ");
        //    sb.Append(" FROM ");
        //    sb.Append("POSTTITLE_MAIN P ");
        //    sb.Append(" LEFT JOIN ");
        //    sb.Append("department_main d ON P.DPMID = D.DPMID  ");
        //    sb.Append(" WHERE 1=1  ");
        //    if (isactive)
        //    {
        //        sb.Append(" AND P.ISACTIVE=1 ");
        //    }
        //    if (ptid.Length > 0)
        //    {
        //        sb.Append(" AND P.PTID = '").Append(ptid).Append("' ");
        //    }
        //    if (ptname.Length > 0)
        //    {
        //        sb.Append(" AND P.PTNAME  LIKE '").Append(ptname).Append("%' ");
        //    }

        //    sb.Append("ORDER BY P.PTID ");
        //    return MySqlHander.SqlSelect(sb.ToString());
        //}
        ////更新岗位信息
        //public String UpatePostTitle(DataSet dsEdit, String userid)
        //{
        //    int executecount = 0;
        //    string rtMsg = "";
        //    DbConnectionmysql connection = new DbConnectionmysql();
        //    connection.mysqlconnect.Open();

        //    MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
        //    try
        //    {
        //        if (dsEdit.Tables["ADD"] != null)
        //        {
        //            foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
        //            {
        //                MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
        //                StringBuilder sb = new StringBuilder();
        //                sb.Append("INSERT INTO  POSTTITLE_MAIN(PTID,PTNAME,PTENAME,PTMARK,DPMID,ISACTIVE,MODIFYER,MODIFYDATE) ");
        //                sb.Append("VALUES(?PTID,?PTNAME,?PTENAME,?PTMARK,?DPMID,?ISACTIVE,?MODIFYER,NOW())");

        //                cmd.CommandText = sb.ToString();
        //                cmd.Transaction = transaction;
        //                cmd.Parameters.Clear();
        //                cmd.Parameters.Add("?PTID", MySqlDbType.String);
        //                cmd.Parameters.Add("?PTNAME", MySqlDbType.String);
        //                cmd.Parameters.Add("?PTENAME", MySqlDbType.String);
        //                cmd.Parameters.Add("?PTMARK", MySqlDbType.String);
        //                cmd.Parameters.Add("?DPMID", MySqlDbType.String);
        //                cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
        //                cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
        //                cmd.Parameters["?PTID"].Value = dr["PTID"].ToString();
        //                cmd.Parameters["?PTNAME"].Value = dr["PTNAME"].ToString();
        //                cmd.Parameters["?PTENAME"].Value = dr["PTENAME"].ToString();
        //                cmd.Parameters["?PTMARK"].Value = dr["PTMARK"].ToString();
        //                cmd.Parameters["?DPMID"].Value = dr["DPMID"].ToString();
        //                int isat = 0;
        //                if ("True".Equals(dr["ISACTIVE"].ToString()) || "1".Equals(dr["ISACTIVE"].ToString()))
        //                {
        //                    isat = 1;
        //                }
        //                cmd.Parameters["?ISACTIVE"].Value = isat;
        //                cmd.Parameters["?MODIFYER"].Value = userid;
        //                executecount += cmd.ExecuteNonQuery();

        //                String strLog = userid + " Add POST_TITLE PTID=" + dr["PTID"].ToString() + "  PTNAME=" + dr["PTNAME"].ToString() + "  PTENAME=" + dr["PTENAME"].ToString() + "  PTMARK=" + dr["PTMARK"].ToString() + "  DPMID=" + dr["DPMID"].ToString() + "  ISACTIVE=" + isat;
        //                LogWrite.Log("SYSTEM", strLog);

        //            }

        //        }
        //        if (dsEdit.Tables["MODIFY"] != null)
        //        {
        //            foreach (DataRow dr in dsEdit.Tables["MODIFY"].Rows)
        //            {
        //                MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
        //                StringBuilder sb = new StringBuilder();
        //                sb.Append("UPDATE  POSTTITLE_MAIN  ");
        //                sb.Append(" SET PTNAME=?PTNAME,PTENAME=?PTENAME,PTMARK=?PTMARK,DPMID=?DPMID,ISACTIVE=?ISACTIVE,MODIFYER=?MODIFYER,MODIFYDATE=NOW() WHERE PTID=?PTID  ");

        //                cmd.CommandText = sb.ToString();
        //                cmd.Transaction = transaction;
        //                cmd.Parameters.Clear();
        //                cmd.Parameters.Add("?PTID", MySqlDbType.String);
        //                cmd.Parameters.Add("?PTNAME", MySqlDbType.String);
        //                cmd.Parameters.Add("?PTENAME", MySqlDbType.String);
        //                cmd.Parameters.Add("?PTMARK", MySqlDbType.String);
        //                cmd.Parameters.Add("?DPMID", MySqlDbType.String);
        //                cmd.Parameters.Add("?ISACTIVE", MySqlDbType.Int32);
        //                cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
        //                cmd.Parameters["?PTID"].Value = dr["PTID"].ToString();
        //                cmd.Parameters["?PTNAME"].Value = dr["PTNAME"].ToString();
        //                cmd.Parameters["?PTENAME"].Value = dr["PTENAME"].ToString();
        //                cmd.Parameters["?PTMARK"].Value = dr["PTMARK"].ToString();
        //                cmd.Parameters["?DPMID"].Value = dr["DPMID"].ToString();
        //                int isat = 0;
        //                if ("True".Equals(dr["ISACTIVE"].ToString()) || "1".Equals(dr["ISACTIVE"].ToString()))
        //                {
        //                    isat = 1;
        //                }
        //                cmd.Parameters["?ISACTIVE"].Value = isat;
        //                cmd.Parameters["?MODIFYER"].Value = userid;
        //                executecount += cmd.ExecuteNonQuery();

        //                String strLog = userid + " Update POST_TITLE PTID=" + dr["PTID"].ToString() + "  PTNAME=" + dr["PTNAME"].ToString() + "  PTENAME=" + dr["PTENAME"].ToString() + "  PTMARK=" + dr["PTMARK"].ToString() + "  DPMID=" + dr["DPMID"].ToString() + "  ISACTIVE=" + isat;
        //                LogWrite.Log("SYSTEM", strLog);


        //            }
        //        }
        //        transaction.Commit();

        //    }
        //    catch (Exception ex)
        //    {
        //        transaction.Rollback();
        //        return ex.ToString();
        //    }
        //    finally
        //    {
        //        connection.mysqlconnect.Close();
        //    }
        //    rtMsg = "Update/Add :" + executecount + " ";
        //    return rtMsg;
        //}
        //按部门码和级次限制取对应部门信息
        public DataSet GetDepartmentByLevel(String dpmid, int level, Boolean isactive)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT  'False' AS \"CHOOSE\",D.* FROM DEPARTMENT_MAIN D  WHERE 1=1 ");
            if (isactive)
            {
                sb.Append(" AND D.ISACTIVE=1 ");
            }
            if (dpmid.Length >= 0)
            {
                sb.Append(" AND D.DPMID LIKE '").Append(dpmid).Append("%' AND LENGTH(D.DPMID)=").Append(level*2+2);
            }
            return MySqlHander.SqlSelect(sb.ToString());
        }
        //通过ID得到部门信息和部门的分级信息
        public DataSet GetDepartmentByID(String dpmid, Boolean isactive)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT D.DPMID,D.DPMNAME,D.DPMENAME,D.DPMMARK,D.DPMOWNER,D.ISACTIVE, ");
            sb.Append(" CASE WHEN LENGTH(IFNULL(D.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(D.DPMID, ''))>=4 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTR(D.DPMID,1,4))  ELSE NULL  END DEPARTMENT1 , ");
            sb.Append(" CASE WHEN LENGTH(IFNULL(D.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(D.DPMID, ''))>=6 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTR(D.DPMID,1,6))  ELSE NULL  END DEPARTMENT2 , ");
            sb.Append(" CASE WHEN LENGTH(IFNULL(D.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(D.DPMID, ''))>=8 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTR(D.DPMID,1,8))  ELSE NULL  END DEPARTMENT3 , ");
            sb.Append(" CASE WHEN LENGTH(IFNULL(D.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(D.DPMID, ''))>=10 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTR(D.DPMID,1,10))  ELSE NULL  END DEPARTMENT4  ");
            sb.Append(" FROM DEPARTMENT_MAIN D ");
            sb.Append(" WHERE 1=1 ");
            if (isactive)
            {
                sb.Append(" AND D.ISACTIVE=1 ");
            }
            if (dpmid.Length >= 0)
            {
                sb.Append(" AND D.DPMID = '").Append(dpmid).Append("' ");
            }
            return MySqlHander.SqlSelect(sb.ToString());
        }
        ////按部门限制得到人员信息
        //public DataSet GetEmployeeByDpm(String strDPMID, Object[] arystrdpmid, String eid, String ename)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append("SELECT  'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\",'False' AS \"CHOOSE\", E.EID,E.ENAME,E.EENAME,CASE E.ESEX WHEN 1 THEN 'M' ELSE 'F' END ESEX,E.DPMID,D.DPMNAME,E.PLID,P.PLNAME,E.EMAIL,E.PHONE,E.TIMEIN,E.TIMEOUT,E.EMARK, ");
        //    sb.Append("CASE WHEN LENGTH(IFNULL(E.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(E.DPMID, ''))>=4 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTRING(E.DPMID,1,4))  ELSE NULL  END DEPARTMENT1 , ");
        //    sb.Append("CASE WHEN LENGTH(IFNULL(E.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(E.DPMID, ''))>=6 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTRING(E.DPMID,1,6))  ELSE NULL  END DEPARTMENT2 , ");
        //    sb.Append("CASE WHEN LENGTH(IFNULL(E.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(E.DPMID, ''))>=8 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTRING(E.DPMID,1,8))  ELSE NULL  END DEPARTMENT3 , ");
        //    sb.Append("CASE WHEN LENGTH(IFNULL(E.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(E.DPMID, ''))>=10 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTRING(E.DPMID,1,10))  ELSE NULL  END DEPARTMENT4 ");
        //    sb.Append("FROM  EMPLOYEE_MAIN E  LEFT JOIN  DEPARTMENT_MAIN D ON E.DPMID = D.DPMID     LEFT JOIN    POSTLEVEL_MAIN P ON E.PLID = P.PLID ");
        //    sb.Append(" WHERE E.ISDEL=0  ");
        //    if (eid.Length > 0)
        //    {
        //        sb.Append("AND E.EID = '").Append(eid).Append("' ");
        //    }

        //    if (ename.Length > 0)
        //    {
        //        sb.Append(" AND E.ENAME LIKE '").Append(ename).Append("%' ");
        //    }
        //    if (strDPMID.Length > 0)
        //    {
        //        sb.Append(" AND E.DPMID LIKE '").Append(strDPMID).Append("%' ");
        //    }
        //    String ulimit="";
        //    if (arystrdpmid.Length > 0)
        //    {
        //        ulimit=ulimit+"AND (";
        //        foreach (String dpmid in arystrdpmid)
        //        {
        //            ulimit = ulimit + "E.DPMID LIKE '" + dpmid + "%' OR ";
        //        }
        //        ulimit=ulimit.Remove(ulimit.LastIndexOf("OR"));
        //        ulimit = ulimit + ") ";
        //    }
        //    sb.Append(ulimit);
        //    return MySqlHander.SqlSelect(sb.ToString());
        //}
        //取已经分配岗位的人员
        //public DataSet GetEmployeeSignPost(String ptid, String eid, String ename, Boolean isdel)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append("SELECT  'False' AS \"ADD\",'False' AS \"MODIFY\",'False' AS \"DELETE\", EP.PTID,EP.EID,E.ENAME,E.EENAME,CASE E.ESEX WHEN 1 THEN 'M' ELSE 'F' END ESEX,E.DPMID,D.DPMNAME,E.PLID,P.PLNAME,E.EMAIL,E.PHONE,E.TIMEIN,E.TIMEOUT,E.EMARK, ");
        //    sb.Append("CASE WHEN LENGTH(IFNULL(E.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(E.DPMID, ''))>=4 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTRING(E.DPMID,1,4))  ELSE NULL  END DEPARTMENT1 , ");
        //    sb.Append("CASE WHEN LENGTH(IFNULL(E.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(E.DPMID, ''))>=6 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTRING(E.DPMID,1,6))  ELSE NULL  END DEPARTMENT2 , ");
        //    sb.Append("CASE WHEN LENGTH(IFNULL(E.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(E.DPMID, ''))>=8 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTRING(E.DPMID,1,8))  ELSE NULL  END DEPARTMENT3 , ");
        //    sb.Append("CASE WHEN LENGTH(IFNULL(E.DPMID, ''))=0 THEN NULL  WHEN LENGTH(IFNULL(E.DPMID, ''))>=10 THEN (SELECT DPMNAME FROM  DEPARTMENT_MAIN  WHERE DPMID = SUBSTRING(E.DPMID,1,10))  ELSE NULL  END DEPARTMENT4 ");
        //    sb.Append("FROM  EMPLOYEE_POSTITLE EP JOIN EMPLOYEE_MAIN E ON EP.EID=E.EID LEFT JOIN  DEPARTMENT_MAIN D ON E.DPMID = D.DPMID     LEFT JOIN    POSTLEVEL_MAIN P ON E.PLID = P.PLID ");
        //    sb.Append(" WHERE 1=1   ");
        //    if (isdel)
        //    {
        //        sb.Append("AND E.ISDEL=0  ");
        //    }
        //    if (eid.Length > 0)
        //    {
        //        sb.Append("AND EP.EID = '").Append(eid).Append("' ");
        //    }

        //    if (ename.Length > 0)
        //    {
        //        sb.Append(" AND E.ENAME LIKE '").Append(ename).Append("%' ");
        //    }
        //    if (ptid.Length > 0)
        //    {
        //        sb.Append(" AND EP.PTID = '").Append(ptid).Append("' ");
        //    }
            
        //    return MySqlHander.SqlSelect(sb.ToString());
        //}
        ////更新岗位人员配置
        //public String UpdatePostEmployee(DataSet dsEdit, String userid)
        //{
        //    int executecount = 0;
        //    string rtMsg = "";
        //    DbConnectionmysql connection = new DbConnectionmysql();
        //    connection.mysqlconnect.Open();

        //    MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
        //    try
        //    {
        //        if (dsEdit.Tables["ADD"] != null)
        //        {
        //            foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
        //            {
        //                MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
        //                StringBuilder sb = new StringBuilder();
        //                sb.Append("INSERT INTO  EMPLOYEE_POSTITLE(EID,PTID,MODIFYER,MODIFYDATE) ");
        //                sb.Append("VALUES(?EID,?PTID,?MODIFYER,NOW())");

        //                cmd.CommandText = sb.ToString();
        //                cmd.Transaction = transaction;
        //                cmd.Parameters.Clear();
        //                cmd.Parameters.Add("?EID", MySqlDbType.String);
        //                cmd.Parameters.Add("?PTID", MySqlDbType.String);
        //                cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
        //                cmd.Parameters["?EID"].Value = dr["EID"].ToString();
        //                cmd.Parameters["?PTID"].Value = dr["PTID"].ToString();
        //                cmd.Parameters["?MODIFYER"].Value = userid;
        //                executecount += cmd.ExecuteNonQuery();

        //                String strLog = userid + " Add Eployee Post EID=" + dr["EID"].ToString() + "  PTID=" + dr["PTID"].ToString();
        //                LogWrite.Log("SYSTEM", strLog);

        //            }

        //        }
        //        if (dsEdit.Tables["DELETE"] != null)
        //        {
        //            foreach (DataRow dr in dsEdit.Tables["DELETE"].Rows)
        //            {
        //                MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
        //                StringBuilder sb = new StringBuilder();
        //                sb.Append("DELETE FROM EMPLOYEE_POSTITLE  ");
        //                sb.Append(" WHERE EID=?EID AND PTID=?PTID");

        //                cmd.CommandText = sb.ToString();
        //                cmd.Transaction = transaction;
        //                cmd.Parameters.Clear();
        //                cmd.Parameters.Add("?EID", MySqlDbType.String);
        //                cmd.Parameters.Add("?PTID", MySqlDbType.String);

        //                cmd.Parameters["?EID"].Value = dr["EID"].ToString();
        //                cmd.Parameters["?PTID"].Value = dr["PTID"].ToString();
        //                executecount += cmd.ExecuteNonQuery();

        //                String strLog = userid + " DELETE  Eployee Post EID=" + dr["EID"].ToString() + "  PTID=" + dr["PTID"].ToString();
        //                LogWrite.Log("SYSTEM", strLog);

        //            }
        //        }
        //        transaction.Commit();

        //    }
        //    catch (Exception ex)
        //    {
        //        transaction.Rollback();
        //        return ex.ToString();
        //    }
        //    finally
        //    {
        //        connection.mysqlconnect.Close();
        //    }
        //    rtMsg = "Update/Add :" + executecount + " ";
        //    return rtMsg;
        //}
        //更新用户信息
        public String UpdateUser(DataSet dsEdit, String userid)
        {
            int executecount = 0;
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                if (dsEdit.Tables["MODIFY"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["MODIFY"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("UPDATE USER_MAIN SET  USERNAME=?USERNAME,PASSWORD=?PASSWORD,PWERROE_TIMES=?PWERROE_TIMES,EMAIL=?EMAIL,PHONE=?PHONE,ISDEL=?ISDEL,MODIFYER=?MODIFYER,MODIFYDATE=NOW() ");
                        sb.Append(" WHERE USERID=?USERID");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?USERID", MySqlDbType.String);
                        cmd.Parameters.Add("?USERNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?PASSWORD", MySqlDbType.String);
                        cmd.Parameters.Add("?PWERROE_TIMES", MySqlDbType.Int32);
                        cmd.Parameters.Add("?EMAIL", MySqlDbType.String);
                        cmd.Parameters.Add("?PHONE", MySqlDbType.String);
                        cmd.Parameters.Add("?ISDEL", MySqlDbType.Int32);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters["?USERID"].Value = dr["USERID"].ToString();
                        cmd.Parameters["?USERNAME"].Value = dr["USERNAME"].ToString();
                        cmd.Parameters["?PASSWORD"].Value = dr["PASSWORD"].ToString();
                        cmd.Parameters["?PWERROE_TIMES"].Value = Int32.Parse(dr["PWERROE_TIMES"].ToString());
                        cmd.Parameters["?EMAIL"].Value = dr["EMAIL"].ToString();
                        cmd.Parameters["?PHONE"].Value = dr["PHONE"].ToString();
                        cmd.Parameters["?ISDEL"].Value = ("True".Equals(dr["ISDEL"].ToString()) || "1".Equals(dr["ISDEL"].ToString()))?1:0;
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " Modify User  USERID=" + dr["USERID"].ToString() + "  USERNAME=" + dr["USERNAME"].ToString() + "  PASSWORD=" + dr["PASSWORD"].ToString() + "  PWERROE_TIMES=" + dr["PWERROE_TIMES"].ToString();
                        strLog = strLog + "  ISDEL=" + dr["ISDEL"].ToString() + "  EMAIL=" + dr["EMAIL"].ToString() + "  PHONE=" + dr["PHONE"].ToString();
                        LogWrite.Log("SYSTEM", strLog);

                    }

                }
                if (dsEdit.Tables["ADD"] != null)
                {
                    foreach (DataRow dr in dsEdit.Tables["ADD"].Rows)
                    {
                        MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO USER_MAIN(USERID, USERNAME,PASSWORD,PWERROE_TIMES,EMAIL,PHONE,ISDEL,MODIFYER,MODIFYDATE) ");
                        sb.Append(" VALUES(?USERID,?USERNAME,?PASSWORD,?PWERROE_TIMES,?EMAIL,?PHONE,?ISDEL,?MODIFYER,NOW()) ");

                        cmd.CommandText = sb.ToString();
                        cmd.Transaction = transaction;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("?USERID", MySqlDbType.String);
                        cmd.Parameters.Add("?USERNAME", MySqlDbType.String);
                        cmd.Parameters.Add("?PASSWORD", MySqlDbType.String);
                        cmd.Parameters.Add("?PWERROE_TIMES", MySqlDbType.Int32);
                        cmd.Parameters.Add("?EMAIL", MySqlDbType.String);
                        cmd.Parameters.Add("?PHONE", MySqlDbType.String);
                        cmd.Parameters.Add("?ISDEL", MySqlDbType.Int32);
                        cmd.Parameters.Add("?MODIFYER", MySqlDbType.String);
                        cmd.Parameters["?USERID"].Value = dr["USERID"].ToString();
                        cmd.Parameters["?USERNAME"].Value = dr["USERNAME"].ToString();
                        cmd.Parameters["?PASSWORD"].Value = dr["PASSWORD"].ToString();
                        cmd.Parameters["?PWERROE_TIMES"].Value = Int32.Parse(dr["PWERROE_TIMES"].ToString().Length <=0 ? "0" : dr["PWERROE_TIMES"].ToString());
                        cmd.Parameters["?EMAIL"].Value = dr["EMAIL"].ToString();
                        cmd.Parameters["?PHONE"].Value = dr["PHONE"].ToString();
                        cmd.Parameters["?ISDEL"].Value = ("True".Equals(dr["ISDEL"].ToString()) || "1".Equals(dr["ISDEL"].ToString())) ? 1 : 0;
                        cmd.Parameters["?MODIFYER"].Value = userid;
                        executecount += cmd.ExecuteNonQuery();

                        String strLog = userid + " ADD User  USERID=" + dr["USERID"].ToString() + "  USERNAME=" + dr["USERNAME"].ToString() + "  PASSWORD=" + dr["PASSWORD"].ToString() + "  PWERROE_TIMES=" + dr["PWERROE_TIMES"].ToString();
                        strLog = strLog + "  ISDEL=" + dr["ISDEL"].ToString() + "  EMAIL=" + dr["EMAIL"].ToString() + "  PHONE=" + dr["PHONE"].ToString();
                        LogWrite.Log("SYSTEM", strLog);
                    }
                }
                transaction.Commit();

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Update/Add :" + executecount + " ";
            return rtMsg;
        }
        //通过密码得到用户
        public DataSet GetUserByPW(String userid, String password)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT U.USERID, U.USERNAME,U.PASSWORD,U.PWERROE_TIMES,U.ISDEL,U.MODIFYER,DATE_FORMAT(U.MODIFYDATE,'%Y-%m-%d %H:%i:%s') MODIFYDATE FROM USER_MAIN U WHERE U.ISDEL=0 ");
            sb.Append(" AND U.USERID='").Append(userid).Append("' ");
            sb.Append(" AND U.PASSWORD='").Append(password).Append("' ");
            return MySqlHander.SqlSelect(sb.ToString());
        }
        //记录登录退出情况
        public String UpdateLoginfo(String userid, String username, String ip, String hostname, String domainname, String actiontype)
        {
            int executecount = 0;
            string rtMsg = "";
            DbConnectionmysql connection = new DbConnectionmysql();
            connection.mysqlconnect.Open();

            MySqlTransaction transaction = (MySqlTransaction)connection.mysqlconnect.BeginTransaction();
            try
            {
                MySqlCommand cmd = (MySqlCommand)connection.mysqlconnect.CreateCommand();
                StringBuilder sb = new StringBuilder();
                sb.Append("INSERT INTO LOGINOUT_RECORD(USERID,USERNAME,ACTIONTYPE,ACTIONTIME,HOSTIP,HOSTNAME,DOMAINNAME )  ");
                sb.Append(" VALUES(?USERID,?USERNAME,?ACTIONTYPE,NOW(),?HOSTIP,?HOSTNAME,?DOMAINNAME)");

                cmd.CommandText = sb.ToString();
                cmd.Transaction = transaction;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("?USERID", MySqlDbType.String);
                cmd.Parameters.Add("?USERNAME", MySqlDbType.String);
                cmd.Parameters.Add("?ACTIONTYPE", MySqlDbType.String);
                cmd.Parameters.Add("?HOSTIP", MySqlDbType.String);
                cmd.Parameters.Add("?HOSTNAME", MySqlDbType.String);
                cmd.Parameters.Add("?DOMAINNAME", MySqlDbType.String);

                cmd.Parameters["?USERID"].Value = userid;
                cmd.Parameters["?USERNAME"].Value = username;
                cmd.Parameters["?ACTIONTYPE"].Value = actiontype;
                cmd.Parameters["?HOSTIP"].Value = ip;
                cmd.Parameters["?HOSTNAME"].Value = hostname;
                cmd.Parameters["?DOMAINNAME"].Value = domainname;
                executecount += cmd.ExecuteNonQuery();

                String strLog = userid + "  " + actiontype + "   SYSTEM  USERNAME=" + username + "  HOSTIP=" + ip + "  HOSTNAME=" + hostname + "  DOMAINNAME=" + domainname;
                LogWrite.Log("SYSTEM", strLog);
              
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ex.ToString();
            }
            finally
            {
                connection.mysqlconnect.Close();
            }
            rtMsg = "Update/Add :" + executecount + " ";
            return rtMsg;
        }
        //得到用户的菜单权限
        public DataSet GetUserMenu(String userid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT M.MID FROM  GROUP_MENU M WHERE M.GID IN (SELECT G.GID FROM USER_GROUP G WHERE G.USERID='").Append(userid).Append("') AND M.ISVISIBLE =1 ");
                       
            return MySqlHander.SqlSelect(sb.ToString());
        }
        //取用户部门权限
        //public DataSet GetUserAuthority(String userid)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append("SELECT M.MID FROM  GROUP_MENU M WHERE M.GID IN (SELECT G.GID FROM USER_GROUP G WHERE G.USERID='").Append(userid).Append("') AND M.ISVISIBLE =1 ");

        //    return MySqlHander.SqlSelect(sb.ToString());
        //}

    /*/
    }
}
